#!/bin/bash
set -uo pipefail

#==============================================================#
# File      :   pg-init
# Ctime     :   2020-12-16
# Mtime     :   2020-12-17
# Desc      :   init postgres cluster (after creation)
# Path      :   /pg/bin/pg-init
# Depend    :   psql, /pg/bin/pg-misc-utils
# Author    :   Vonng(fengruohang@outlook.com)
# Copyright (C) 2018-2022 Ruohang Feng
#==============================================================#

PROG_NAME="$(basename $0))"
PROG_DIR="$(cd $(dirname $0) && pwd)"

#==============================================================#
#                           Parameters                         #
#==============================================================#
PG_DBSU='postgres'
PG_REPLICATION_USERNAME='replicator'
PG_REPLICATION_PASSWORD='DBUser.Replicator'
PG_MONITOR_USERNAME='dbuser_monitor'
PG_MONITOR_PASSWORD='DBUser.Monitor'
PG_ADMIN_USERNAME='dbuser_admin'
PG_ADMIN_PASSWORD='DBUser.Admin'
PG_MONITOR_SCHEMA='monitor'
PG_MONITOR_EXTENSIONS='pg_stat_statements pgstattuple pg_qualstats pg_buffercache pageinspect pg_prewarm pg_visibility pg_freespacemap pg_repack'
PG_DEFAULT_SCHEMA='public'
PG_DEFAULT_EXTENSIONS='tablefunc postgres_fdw file_fdw btree_gist btree_gin pg_trgm'

#==============================================================#
#                             Usage                            #
#==============================================================#
function usage() {
	cat <<-'EOF'
		NAME
			pg-init   -- init postgres on a newly created cluster

		SYNOPSIS
			pg-init

		DESCRIPTION
			pg-init will create default roles and users, setup default
			privileges and install extensions and monitor views. It will
			properly set default template database and postgres database.
			It requires a connectible postgres as target

		EXAMPLES
			pg-init

	EOF
	exit 1
}

#==============================================================#
#                             Main                             #
#==============================================================#
function pg-init(){

	# --------------------- #
	# -   default roles   - #
	# --------------------- #
	# pg-create-role <username> [option] [comments]
	/pg/bin/pg-create-role dbrole_readonly  NOLOGIN 'role for readonly access'
	/pg/bin/pg-create-role dbrole_readwrite NOLOGIN 'role for readwrite access'
	/pg/bin/pg-create-role dbrole_admin     NOLOGIN 'role for object creation'

	# pg-create-user <username> [password] [option] [comments]
	/pg/bin/pg-create-user "${PG_DBSU}"                  NULL                           'SUPERUSER'                     'system superuser'
	/pg/bin/pg-create-user "${PG_REPLICATION_USERNAME}" "${PG_REPLICATION_PASSWORD}"    'REPLICATION'                   'system replicator'
	/pg/bin/pg-create-user "${PG_MONITOR_USERNAME}"     "${PG_MONITOR_PASSWORD}"        'CONNECTION LIMIT 10'           'system monitor user'
	/pg/bin/pg-create-user "${PG_ADMIN_USERNAME}"       "${PG_ADMIN_PASSWORD}"          'CREATEDB CREATEROLE BYPASSRLS' 'system admin user'

	# --------------------- #
	# -   default grant   - #
	# --------------------- #
	# pg-grant-role <username> [role...]
	/pg/bin/pg-grant-role dbrole_readwrite              dbrole_readonly
	/pg/bin/pg-grant-role dbrole_admin                  dbrole_readwrite

	/pg/bin/pg-grant-role "${PG_ADMIN_USERNAME}"        dbrole_admin
	/pg/bin/pg-grant-role "${PG_REPLICATION_USERNAME}"  dbrole_readonly
	/pg/bin/pg-grant-role "${PG_MONITOR_USERNAME}"      dbrole_readonly pg_monitor

	# --------------------- #
	# - default privilege - #
	# --------------------- #
	# pg-default-privilege <username> [database=template1]
	/pg/bin/pg-default-privilege postgres               template1
	/pg/bin/pg-default-privilege "${PG_ADMIN_USERNAME}" template1

	/pg/bin/pg-default-privilege postgres               postgres
	/pg/bin/pg-default-privilege "${PG_ADMIN_USERNAME}" postgres

	# --------------------- #
	# -  default schema   - #
	# --------------------- #
	pg-create-schema template1 "${PG_MONITOR_SCHEMA}"
	pg-create-schema template1 "${PG_DEFAULT_SCHEMA}"
	pg-create-schema postgres  "${PG_MONITOR_SCHEMA}"
	pg-create-schema postgres  "${PG_DEFAULT_SCHEMA}"

	# --------------------- #
	# - default extension - #
	# --------------------- #
	# pg-extension <database> <schema> <extensions>
	/pg/bin/pg-create-extension template1 "${PG_MONITOR_SCHEMA}" ${PG_MONITOR_EXTENSIONS}
	/pg/bin/pg-create-extension template1 "${PG_DEFAULT_SCHEMA}" ${PG_DEFAULT_EXTENSIONS}
	/pg/bin/pg-create-extension postgres  "${PG_MONITOR_SCHEMA}" ${PG_MONITOR_EXTENSIONS}
	/pg/bin/pg-create-extension postgres  "${PG_DEFAULT_SCHEMA}" ${PG_DEFAULT_EXTENSIONS}

	# --------------------- #
	# -   monitor view    - #
	# --------------------- #
	# TODO: use pg-deploy scripts
	psql template1 -qAXtf /pg/conf/monitor.sql
	psql postgres  -qAXtf /pg/conf/monitor.sql
}

pg-init